IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveMachine' AND type = 'P')
     DROP PROCEDURE dbo.SaveMachine
GO

CREATE PROCEDURE dbo.SaveMachine
     @PackageExecutionId          bigint,
     @ComputerNamePhysicalNetBIOS varchar(128) = NULL,
     @MachineName                 varchar(128) = NULL,
     @FQMachineName               varchar(255) = NULL,
     @PhysicalCPUCount            int          = NULL,
     @PhysicalCPUCoreCount        int          = NULL,
     @LogicalCPUCoreCount         int          = NULL,
     @MaxCPUSpeedMHz              int          = NULL,
     @OSVersion                   VARCHAR(30)  = NULL,
     @OSLevel                     varchar(30)  = NULL,
     @OSArchitecture              varchar(10)  = NULL,
     @OSEdition                   varchar(128) = NULL,
     @TotalMachineMemoryMB        int          = NULL,
     @RSVDMachineMemoryMB         int          = NULL,
     @ClusterVirtualNameFlag      tinyint      = NULL,
     @SuppressResultSetFlag       tinyint      = 0,
     @MachineId                   int          = NULL OUTPUT
AS
/*
 *
 * SaveSrvrMachine
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 09/29/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @CalcMachineName varchar(128)

     SET @MachineId = NULL

     -- Determine Calculated machine name by evaluating @ComputerNamePhysicalNetBIOS, 
     -- and then @MachineName in that order
     SELECT @CalcMachineName = COALESCE(@ComputerNamePhysicalNetBIOS, @MachineName )

     IF ( @CalcMachineName IS NULL )
     BEGIN -- We don't have a name so we are done.  We will take no action.
          RETURN 
     END

     -- If we didn't get an FQ name we make FQ = name
     SELECT @FQMachineName = ISNULL( @FQMachineName, @CalcMachineName )

     -- Calculate Reserved Memory if needed
     IF ( @RSVDMachineMemoryMB IS NULL )
     BEGIN
          SELECT @RSVDMachineMemoryMB = @RSVDMachineMemoryMB
          FROM   dbo.Machine
          WHERE  MachineName = @CalcMachineName

          -- If its still null we'll try and calc it..
          SELECT @RSVDMachineMemoryMB = COALESCE( @RSVDMachineMemoryMB, dbo.GetRSVDMemoryMBByTotalMemoryMB( @TotalMachineMemoryMB ) )
     END

     -- Check to see if the machine is stored
     IF EXISTS ( SELECT 'X'
                 FROM   dbo.Machine 
                 WHERE  MachineName = @CalcMachineName )
     BEGIN -- Existing record
          UPDATE dbo.Machine
          SET    PackageExecutionId     = @PackageExecutionId,
                 FQMachineName          = CASE WHEN @FQMachineName IS NULL AND FQMachineName IS NOT NULL
                                               THEN FQMachineName
                                               WHEN @FQMachineName =''
                                               THEN NULL
                                               ELSE @FQMachineName
                                          END,
                 PhysicalCPUCount       = CASE WHEN @PhysicalCPUCount IS NULL AND PhysicalCPUCount IS NOT NULL
                                               THEN PhysicalCPUCount
                                               WHEN @PhysicalCPUCount = 0
                                               THEN NULL
                                               ELSE @PhysicalCPUCount
                                          END,
                 PhysicalCPUCoreCount   = CASE WHEN @PhysicalCPUCoreCount IS NULL AND PhysicalCPUCoreCount IS NOT NULL
                                               THEN PhysicalCPUCoreCount
                                               WHEN @PhysicalCPUCoreCount = 0
                                               THEN NULL
                                               ELSE @PhysicalCPUCoreCount
                                          END,
                 LogicalCPUCoreCount    = CASE WHEN @LogicalCPUCoreCount IS NULL AND LogicalCPUCoreCount IS NOT NULL
                                               THEN LogicalCPUCoreCount
                                               WHEN @LogicalCPUCoreCount = 0
                                               THEN NULL
                                               ELSE @LogicalCPUCoreCount
                                          END,
                 MaxCPUSpeedMHz         = CASE WHEN @MaxCPUSpeedMHz IS NULL AND MaxCPUSpeedMHz IS NOT NULL
                                               THEN MaxCPUSpeedMHz
                                               WHEN @MaxCPUSpeedMHz = 0
                                               THEN NULL
                                               ELSE @MaxCPUSpeedMHz
                                          END,
                 OSVersion              = CASE WHEN @OSVersion IS NULL AND OSVersion IS NOT NULL
                                               THEN OSVersion
                                               ELSE @OSVersion
                                          END,
                 OSLevel                = CASE WHEN @OSLevel IS NULL AND OSLevel IS NOT NULL
                                               THEN OSLevel
                                               ELSE @OSLevel
                                          END,
                 OSArchitecture         = CASE WHEN @OSArchitecture IS NULL AND OSArchitecture IS NOT NULL
                                               THEN OSArchitecture
                                               ELSE @OSArchitecture
                                          END,
                 OSEdition              = CASE WHEN @OSEdition IS NULL AND OSEdition IS NOT NULL
                                               THEN OSEdition
                                               ELSE @OSEdition
                                          END,
                 TotalMachineMemoryMB   = CASE WHEN @TotalMachineMemoryMB IS NULL AND TotalMachineMemoryMB IS NOT NULL
                                               THEN TotalMachineMemoryMB
                                               WHEN @TotalMachineMemoryMB = 0
                                               THEN NULL
                                               ELSE @TotalMachineMemoryMB
                                          END,
                 RSVDMachineMemoryMB    = CASE WHEN @RSVDMachineMemoryMB IS NULL AND RSVDMachineMemoryMB IS NOT NULL
                                               THEN RSVDMachineMemoryMB
                                               WHEN @RSVDMachineMemoryMB IS NULL AND RSVDMachineMemoryMB IS NULL
                                               THEN dbo.GetRSVDMemoryMBByTotalMemoryMB( @TotalMachineMemoryMB )
                                               WHEN @RSVDMachineMemoryMB = 0
                                               THEN dbo.GetRSVDMemoryMBByTotalMemoryMB( @TotalMachineMemoryMB )
                                               ELSE @RSVDMachineMemoryMB
                                          END,
                 ClusterVirtualNameFlag = CASE WHEN @ClusterVirtualNameFlag IS NULL AND ClusterVirtualNameFlag IS NOT NULL
                                               THEN ClusterVirtualNameFlag
                                               ELSE @ClusterVirtualNameFlag
                                          END,
                 LastModDt              = CURRENT_TIMESTAMP
          FROM   dbo.Machine 
          WHERE  MachineName = @CalcMachineName
          --AND    ( 1=0
          AND    ( ISNULL( PhysicalCPUCount,       -1 ) != ISNULL( @PhysicalCPUCount,       -1 )
                OR ISNULL( PhysicalCPUCoreCount,   -1 ) != ISNULL( @PhysicalCPUCoreCount,   -1 )
                OR ISNULL( LogicalCPUCoreCount,    -1 ) != ISNULL( @LogicalCPUCoreCount,    -1 )
                OR ISNULL( MaxCPUSpeedMHz,         -1 ) != ISNULL( @MaxCPUSpeedMHz,         -1 )
                OR ISNULL( TotalMachineMemoryMB,   -1 ) != ISNULL( @TotalMachineMemoryMB,   -1 )
                OR ISNULL( RSVDMachineMemoryMB,    -1 ) != ISNULL( @RSVDMachineMemoryMB,    -1 )
               -- We only need to test ineqity for non-null fields.
                OR ( ClusterVirtualNameFlag      != @ClusterVirtualNameFlag )
               -- We are not testing for the case where we have a value and would set the value to NULL
                OR ( FQMachineName  IS     NULL AND @FQMachineName IS NOT NULL 
               -- OR FQMachineName  IS NOT NULL AND @FQMachineName IS     NULL 
                  OR FQMachineName               != @FQMachineName )
                OR ( OSVersion      IS     NULL AND @OSVersion      IS NOT NULL 
               -- OR OSVersion      IS NOT NULL AND @OSVersion      IS     NULL 
                  OR OSVersion                   != @OSVersion )
                OR ( OSLevel        IS     NULL AND @OSLevel        IS NOT NULL 
               -- OR OSLevel        IS NOT NULL AND @OSLevel        IS     NULL 
                  OR OSLevel                     != @OSLevel )
                OR ( OSArchitecture IS     NULL AND @OSArchitecture IS NOT NULL 
               -- OR OSArchitecture IS NOT NULL AND @OSArchitecture IS     NULL 
                  OR OSArchitecture              != @OSArchitecture )
                OR ( OSEdition      IS     NULL AND @OSEdition      IS NOT NULL 
               -- OR OSEdition      IS NOT NULL AND @OSEdition      IS     NULL 
                  OR OSEdition                   != @OSEdition )
                 ) -- Closing Paren

          SELECT @MachineId = MachineId
          FROM   dbo.Machine 
          WHERE  MachineName = @CalcMachineName 
     END
     ELSE
     BEGIN -- Do Insert
          INSERT dbo.Machine 
               ( PackageExecutionId,
                 EnabledFlag,
                 MachineName,
                 FQMachineName,
                 PhysicalCPUCount,
                 PhysicalCPUCoreCount,
                 LogicalCPUCoreCount,
                 MaxCPUSpeedMHz,
                 OSVersion,
                 OSLevel,
                 OSArchitecture,
                 OSEdition,
                 TotalMachineMemoryMB,
                 RSVDMachineMemoryMB )
          SELECT @PackageExecutionId,
                 1, -- EnabledFlag
                 @CalcMachineName,
                 @FQMachineName,
                 @PhysicalCPUCount,
                 @PhysicalCPUCoreCount,
                 @LogicalCPUCoreCount,
                 @MaxCPUSpeedMHz,
                 @OSVersion,
                 @OSLevel,
                 @OSArchitecture,
                 @OSEdition,
                 @TotalMachineMemoryMB,
                 @RSVDMachineMemoryMB

          SELECT @MachineId = SCOPE_IDENTITY()
     END

     IF ( @SuppressResultSetFlag != 1 ) 
     BEGIN
          SELECT MachineId = @MachineId 
     END
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Saves a machine record if is is not already existing in the list, this represents a physical (or virtual) machine and not necessarily a database instance.  This sproc is called by SaveSrvrMachineSrvrInstncMachine.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveMachine;
GO
